-- EXEC SP_PRODUCTION_REPORT_BY_MONTH '04', '2008'
ALTER PROC SP_PRODUCTION_REPORT_BY_MONTH ( @ASOFMonth	VARCHAR(50), @ASOFYEAR VARCHAR(50) ) AS
BEGIN
	/*DECLARE @RESULTS TABLE ( [BASE PART] VARCHAR(50), [Base Product ID] INT, [Wafer Input] INT,
			[Effective Die] INT,[Fab Wafer Qty] INT, [Fab Effective Die] INT, [Fab Cost] NUMERIC(18,2), [Month Start Wafer Qty] INT, [Month Start Effective Die] INT, [Die Bank Qty] INT, [Die Bank Cost] NUMERIC(18,2), [Assembly Die Qty] INT, [Assembly Die Cost] Numeric(18,2), [Test-FT Die Qty] INT, [Test Die Cost] NUMERIC(18,2),
			[FINISHED GOODS Qty] INT, [Finished Goods Cost] Numeric(18,2), [Total Shipped Units] INT, [Total Backlog] INT )*/
	/*DECLARE @RESULTS TABLE ( [BASE PART] VARCHAR(50), [Base Product ID] INT, [Wafer Input] INT,
			[Effective Die] INT, [Wafer Input Cost] Numeric(18,2), [Die Bank Qty] INT, [Die Bank Cost] NUMERIC(18,2), [Assembly Die Qty] INT, [Assembly Die Cost] Numeric(18,2), [Test-FT Die Qty] INT, [Test Die Cost] NUMERIC(18,2),
			[FINISHED GOODS Qty] INT, [Finished Goods Cost] Numeric(18,2), [Total Shipped Units] INT, [Total Shipment Cost] NUMERIC(18,2), [Total Backlog] INT )*/
	DECLARE @RESULTS TABLE ( [BASE PART] VARCHAR(50), [Base Product ID] INT, [Wafer Input] INT,
			[Effective Die] INT, [Die Bank Start Qty] INT, [Die Bank End Qty] INT, [Assembly Die Start Qty] INT, [Assembly Die End Qty] INT, [Test-FT Die Start Qty] INT, [Test-FT Die End Qty] INT,
			[FINISHED GOODS Start Qty] INT, [Finished Goods End Qty] INT, [Total Shipped Units] INT, [Total Backlog] INT )
	DECLARE @START_DATE 	DATETIME
	DECLARE @END_DATE 		DATETIME
	DECLARE @MONTH_START_DATE 	DATETIME
	DECLARE @MONTH_END_DATE 	DATETIME
	DECLARE @ASOFDATE 		VARCHAR(50)
	DECLARE @LOT_CURSOR		CURSOR
	DECLARE @Shipment_CURSOR CURSOR
	DECLARE @FAB_Transit_CURSOR CURSOR
	DECLARE @FAB_Month_Start_CURSOR CURSOR
	DECLARE @Qty_IN			INT
	DECLARE @Wafer_Qty_In	INT
	DECLARE @Month_Start_Wafer_Qty_In	INT
	DECLARE @ProcessStep_ID INT
	DECLARE @Gross_Die_Wafer INT
	DECLARE @Lot_Status		VARCHAR(50)
	DECLARE @P_Type			VARCHAR(50)
	DECLARE @P_Parent_Product_ID INT
	DECLARE @PS_Type_Name	VARCHAR(50)
	
	SET @ASOFDATE = @ASOFMonth + '/'+ '01/' + @ASOFYEAR
	SET @ASOFDATE = DATEADD( d, -1, @ASOFDATE )

	SET @START_DATE = @ASOFDATE
	SET @END_DATE = DATEADD( hh, 23, @ASOFDATE )
	SET @END_DATE = DATEADD( mi, 59, @END_DATE )


	--INSERT INTO @RESULTS ( [BASE PART] ) SELECT P.Part_Number FROM Product P, Process_Step PS WHERE P.ProcessStep_Id = PS.ProcessStep_ID AND PS.PS_Type_Name = 'FAB'
	--INSERT INTO @RESULTS ( [BASE PART] ) SELECT P.Part_Number FROM Product P WHERE P.P_Type = 'BOM'
	INSERT INTO @RESULTS ( [BASE PART], [Base Product ID] ) SELECT Part_Number, CASE WHEN P_Parent_Product_ID > 1 THEN P_Parent_Product_ID ELSE Product_ID END  FROM Product WHERE ( P_Parent_Product_Id = -1 OR P_Parent_Product_Id IS NULL ) AND ( P_Type IS NULL OR P_Type = 'BOM' ) AND OBSOLETE = 1

	/*SET @LOT_CURSOR = CURSOR FOR SELECT SUM( ISNULL( WAFER_QTY_IN, 0 ) )AS Total_WAFER_QTY_IN, SUM(ISNULL( Lot_Cummulative_Cost, 0)), SUM( ISNULL( QTY_IN, 0 ) )AS Total_Die_QTY_IN, SUM(ISNULL( Lot_Cummulative_Cost, 0)), P.PROCESSSTEP_ID, LTH.Lot_Current_Status, 
	ISNULL(P.P_Type, 'CHIP') AS P_Type, CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID 
	FROM Lot_Table_History LTH, Product P 
	WHERE LTH.ProcessStep_Id = P.ProcessStep_ID AND P.Obsolete != 0 AND 
	LTH.TimeStamp = ( SELECT MAX(LTH1.Timestamp) FROM LOT_TABLE_HISTORY LTH1 WHERE lth1.lot_id = lth.lot_id and LTH1.TIMESTAMP BETWEEN @START_DATE AND @END_DATE  AND ( LTH1.LOT_CURRENT_STATUS = 'RUNNING' OR LTH1.LOT_CURRENT_STATUS = 'HOLD') )
	GROUP BY P.PROCESSSTEP_ID, LTH.Lot_Current_Status, ISNULL(P.P_Type, 'CHIP'), P.P_Parent_Product_ID, P.Product_ID
	UNION 
	SELECT CASE WHEN PS.PS_Type_Name = 'Fab' THEN SUM( ISNULL( SLH.Ship_Qty , 0 ) ) ELSE 0 END AS Total_WAFER_QTY_IN, 0.0,
	CASE WHEN PS.PS_Type_Name <> 'Fab' THEN SUM( ISNULL( SLH.Ship_Qty , 0 ) ) ELSE 0 END AS Total_Die_QTY_IN, 0.0, P.ProcessStep_Id, SLH.Status, 
	ISNULL(P.P_Type, 'CHIP') AS P_Type, CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID 
	FROM Shipped_Lot_History SLH, Lot_Table LT, Product P, Process_Step PS
	WHERE SLH.Lot_ID = LT.Lot_Id AND Lt.ProcessStep_Id = P.ProcessStep_Id AND P.ProcessStep_id = PS.ProcessStep_Id AND P.Obsolete != 0 
	AND SLH.Status = 'TRANSIT'	
	AND SLH.TimeStamp BETWEEN @START_DATE AND @END_DATE 
	GROUP BY P.PROCESSSTEP_ID, SLH.Status, PS.PS_Type_Name, ISNULL(P.P_Type, 'CHIP'), P.P_Parent_Product_ID, P.Product_ID*/


	SET @LOT_CURSOR = CURSOR FOR SELECT SUM( ISNULL( WAFER_QTY_IN, 0 ) )AS Total_WAFER_QTY_IN, SUM( ISNULL( QTY_IN, 0 ) )AS Total_Die_QTY_IN, P.PROCESSSTEP_ID, LTH.Lot_Current_Status, 
	ISNULL(P.P_Type, 'CHIP') AS P_Type, CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID 
	FROM Lot_Table_History LTH, Product P 
	WHERE LTH.ProcessStep_Id = P.ProcessStep_ID AND P.Obsolete != 0 AND 
	LTH.TimeStamp = ( SELECT MAX(LTH1.Timestamp) FROM LOT_TABLE_HISTORY LTH1 WHERE lth1.lot_id = lth.lot_id and LTH1.TIMESTAMP BETWEEN @START_DATE AND @END_DATE  AND ( LTH1.LOT_CURRENT_STATUS = 'RUNNING' OR LTH1.LOT_CURRENT_STATUS = 'HOLD') )
	GROUP BY P.PROCESSSTEP_ID, LTH.Lot_Current_Status, ISNULL(P.P_Type, 'CHIP'), P.P_Parent_Product_ID, P.Product_ID
	UNION 
	SELECT CASE WHEN PS.PS_Type_Name = 'Fab' THEN SUM( ISNULL( SLH.Ship_Qty , 0 ) ) ELSE 0 END AS Total_WAFER_QTY_IN,
	CASE WHEN PS.PS_Type_Name <> 'Fab' THEN SUM( ISNULL( SLH.Ship_Qty , 0 ) ) ELSE 0 END AS Total_Die_QTY_IN, P.ProcessStep_Id, SLH.Status, 
	ISNULL(P.P_Type, 'CHIP') AS P_Type, CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID 
	FROM Shipped_Lot_History SLH, Lot_Table LT, Product P, Process_Step PS
	WHERE SLH.Lot_ID = LT.Lot_Id AND Lt.ProcessStep_Id = P.ProcessStep_Id AND P.ProcessStep_id = PS.ProcessStep_Id AND P.Obsolete != 0 
	AND SLH.Status = 'TRANSIT'	
	AND SLH.TimeStamp BETWEEN @START_DATE AND @END_DATE 
	GROUP BY P.PROCESSSTEP_ID, SLH.Status, PS.PS_Type_Name, ISNULL(P.P_Type, 'CHIP'), P.P_Parent_Product_ID, P.Product_ID
	OPEN @LOT_CURSOR
	--FETCH NEXT FROM @LOT_CURSOR INTO @Wafer_Qty_In, @Wafer_COST, @Qty_IN, @Die_Cost, @ProcessStep_ID, @Lot_Status, @P_Type, @P_Parent_Product_ID
	FETCH NEXT FROM @LOT_CURSOR INTO @Wafer_Qty_In, @Qty_IN, @ProcessStep_ID, @Lot_Status, @P_Type, @P_Parent_Product_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @PS_Type_Name = PS_Type_Name FROM Process_Step WHERE ProcessStep_ID = @ProcessStep_ID

		/*IF @PS_Type_Name = 'Fab'
		BEGIN
			SET @Gross_Die_Wafer = 0
			SELECT @Gross_Die_Wafer = ISNULL(Parameter_Value, 0) FROM Process_parameter WHERE ProcessStep_Id = @ProcessStep_ID AND Parameter_Key = 'Gross Die/Wafer'
			Update @RESULTS SET [Fab Wafer Qty] = ISNULL( [Fab Wafer Qty], 0) + @Wafer_Qty_In, [Fab Effective Die] = ISNULL([Fab Effective Die], 0) + ( ISNULL(@Wafer_Qty_In, 0) * @Gross_Die_Wafer ), [Fab Cost] = ISNULL( [Fab Cost], 0 ) + @Wafer_COST WHERE [Base Product ID] = @P_Parent_Product_ID
		END*/
		
		IF @PS_Type_Name = 'Die Bank'
		BEGIN
			--Update @RESULTS SET [Die Bank Qty] = ISNULL( [Die Bank Qty], 0) + @Qty_IN , [Die Bank Cost] = ISNULL([Die Bank Cost], 0) + @Die_Cost  WHERE [Base Product ID] = @P_Parent_Product_ID
			Update @RESULTS SET [Die Bank Start Qty] = ISNULL( [Die Bank Start Qty], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		END
		IF @PS_Type_Name = 'Assembly'
		BEGIN	
			/*IF @P_Type = 'BOM'
			BEGIN
				SET @Die_Cost = @Qty_IN * 0.11
			END
			Update @RESULTS SET [Assembly Die Qty] = ISNULL( [Assembly Die Qty], 0) + @Qty_IN,  [Assembly Die Cost] = ISNULL( [Assembly Die Cost], 0) + @Die_Cost WHERE [Base Product ID] = @P_Parent_Product_ID*/
			Update @RESULTS SET [Assembly Die Start Qty] = ISNULL( [Assembly Die Start Qty], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		END

		IF @PS_Type_Name = 'Test'
		BEGIN
			--Update @RESULTS SET [Test-FT Die Qty] = ISNULL( [Test-FT Die Qty], 0) + @Qty_IN, [Test Die Cost] = ISNULL([Test Die Cost], 0) + @Die_Cost WHERE [Base Product ID] = @P_Parent_Product_ID
			Update @RESULTS SET [Test-FT Die Start Qty] = ISNULL( [Test-FT Die Start Qty], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		END
		IF @PS_Type_Name = 'Finished Goods'
		BEGIN
			--Update @RESULTS SET [FINISHED GOODS Qty] = ISNULL( [FINISHED GOODS Qty], 0) + @Qty_IN, [Finished Goods Cost] = ISNULL([Finished Goods Cost], 0) + @Die_Cost WHERE [Base Product ID] = @P_Parent_Product_ID
			Update @RESULTS SET [FINISHED GOODS Start Qty] = ISNULL( [FINISHED GOODS Start Qty], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		END
		--FETCH NEXT FROM @LOT_CURSOR INTO  @Wafer_Qty_In, @Wafer_COST, @Qty_IN, @Die_Cost, @ProcessStep_ID, @Lot_Status, @P_Type, @P_Parent_Product_ID
		FETCH NEXT FROM @LOT_CURSOR INTO  @Wafer_Qty_In, @Qty_IN, @ProcessStep_ID, @Lot_Status, @P_Type, @P_Parent_Product_ID
	END
	CLOSE @LOT_CURSOR
	DEALLOCATE @LOT_CURSOR

	-- This is for the Month end values

	SET @ASOFDATE = @ASOFMonth + '/'+ '01/' + @ASOFYEAR
	SET @ASOFDATE = DATEADD( m, 1, @ASOFDATE )
	SET @ASOFDATE = DATEADD( d, -1, @ASOFDATE )

	
	SET @START_DATE = @ASOFDATE
	SET @END_DATE = DATEADD( hh, 23, @ASOFDATE )
	SET @END_DATE = DATEADD( mi, 59, @END_DATE )

	PRINT ' START_DATE ########## ' + CONVERT( VARCHAR, @START_DATE )
	PRINT ' END DATE  ########## ' + CONVERT( VARCHAR, @END_DATE )
	
	SET @LOT_CURSOR = CURSOR FOR SELECT SUM( ISNULL( WAFER_QTY_IN, 0 ) )AS Total_WAFER_QTY_IN, SUM( ISNULL( QTY_IN, 0 ) )AS Total_Die_QTY_IN, P.PROCESSSTEP_ID, LTH.Lot_Current_Status, 
	ISNULL(P.P_Type, 'CHIP') AS P_Type, CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID 
	FROM Lot_Table_History LTH, Product P 
	WHERE LTH.ProcessStep_Id = P.ProcessStep_ID AND P.Obsolete != 0 AND 
	LTH.TimeStamp = ( SELECT MAX(LTH1.Timestamp) FROM LOT_TABLE_HISTORY LTH1 WHERE lth1.lot_id = lth.lot_id and LTH1.TIMESTAMP BETWEEN @START_DATE AND @END_DATE  AND ( LTH1.LOT_CURRENT_STATUS = 'RUNNING' OR LTH1.LOT_CURRENT_STATUS = 'HOLD') )
	GROUP BY P.PROCESSSTEP_ID, LTH.Lot_Current_Status, ISNULL(P.P_Type, 'CHIP'), P.P_Parent_Product_ID, P.Product_ID
	UNION 
	SELECT CASE WHEN PS.PS_Type_Name = 'Fab' THEN SUM( ISNULL( SLH.Ship_Qty , 0 ) ) ELSE 0 END AS Total_WAFER_QTY_IN,
	CASE WHEN PS.PS_Type_Name <> 'Fab' THEN SUM( ISNULL( SLH.Ship_Qty , 0 ) ) ELSE 0 END AS Total_Die_QTY_IN, P.ProcessStep_Id, SLH.Status, 
	ISNULL(P.P_Type, 'CHIP') AS P_Type, CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID 
	FROM Shipped_Lot_History SLH, Lot_Table LT, Product P, Process_Step PS
	WHERE SLH.Lot_ID = LT.Lot_Id AND Lt.ProcessStep_Id = P.ProcessStep_Id AND P.ProcessStep_id = PS.ProcessStep_Id AND P.Obsolete != 0 
	AND SLH.Status = 'TRANSIT'	
	AND SLH.TimeStamp BETWEEN @START_DATE AND @END_DATE 
	GROUP BY P.PROCESSSTEP_ID, SLH.Status, PS.PS_Type_Name, ISNULL(P.P_Type, 'CHIP'), P.P_Parent_Product_ID, P.Product_ID
	OPEN @LOT_CURSOR
	--FETCH NEXT FROM @LOT_CURSOR INTO @Wafer_Qty_In, @Wafer_COST, @Qty_IN, @Die_Cost, @ProcessStep_ID, @Lot_Status, @P_Type, @P_Parent_Product_ID
	FETCH NEXT FROM @LOT_CURSOR INTO @Wafer_Qty_In, @Qty_IN, @ProcessStep_ID, @Lot_Status, @P_Type, @P_Parent_Product_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @PS_Type_Name = PS_Type_Name FROM Process_Step WHERE ProcessStep_ID = @ProcessStep_ID

		/*IF @PS_Type_Name = 'Fab'
		BEGIN
			SET @Gross_Die_Wafer = 0
			SELECT @Gross_Die_Wafer = ISNULL(Parameter_Value, 0) FROM Process_parameter WHERE ProcessStep_Id = @ProcessStep_ID AND Parameter_Key = 'Gross Die/Wafer'
			Update @RESULTS SET [Fab Wafer Qty] = ISNULL( [Fab Wafer Qty], 0) + @Wafer_Qty_In, [Fab Effective Die] = ISNULL([Fab Effective Die], 0) + ( ISNULL(@Wafer_Qty_In, 0) * @Gross_Die_Wafer ), [Fab Cost] = ISNULL( [Fab Cost], 0 ) + @Wafer_COST WHERE [Base Product ID] = @P_Parent_Product_ID
		END*/
		
		IF @PS_Type_Name = 'Die Bank'
		BEGIN
			--Update @RESULTS SET [Die Bank Qty] = ISNULL( [Die Bank Qty], 0) + @Qty_IN , [Die Bank Cost] = ISNULL([Die Bank Cost], 0) + @Die_Cost  WHERE [Base Product ID] = @P_Parent_Product_ID
			Update @RESULTS SET [Die Bank End Qty] = ISNULL( [Die Bank End Qty], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		END
		IF @PS_Type_Name = 'Assembly'
		BEGIN	
			/*IF @P_Type = 'BOM'
			BEGIN
				SET @Die_Cost = @Qty_IN * 0.11
			END
			Update @RESULTS SET [Assembly Die Qty] = ISNULL( [Assembly Die Qty], 0) + @Qty_IN,  [Assembly Die Cost] = ISNULL( [Assembly Die Cost], 0) + @Die_Cost WHERE [Base Product ID] = @P_Parent_Product_ID*/
			Update @RESULTS SET [Assembly Die End Qty] = ISNULL( [Assembly Die End Qty], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		END

		IF @PS_Type_Name = 'Test'
		BEGIN
			--Update @RESULTS SET [Test-FT Die Qty] = ISNULL( [Test-FT Die Qty], 0) + @Qty_IN, [Test Die Cost] = ISNULL([Test Die Cost], 0) + @Die_Cost WHERE [Base Product ID] = @P_Parent_Product_ID
			Update @RESULTS SET [Test-FT Die End Qty] = ISNULL( [Test-FT Die End Qty], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		END
		IF @PS_Type_Name = 'Finished Goods'
		BEGIN
			--Update @RESULTS SET [FINISHED GOODS Qty] = ISNULL( [FINISHED GOODS Qty], 0) + @Qty_IN, [Finished Goods Cost] = ISNULL([Finished Goods Cost], 0) + @Die_Cost WHERE [Base Product ID] = @P_Parent_Product_ID
			Update @RESULTS SET [FINISHED GOODS End Qty] = ISNULL( [FINISHED GOODS End Qty], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		END
		--FETCH NEXT FROM @LOT_CURSOR INTO  @Wafer_Qty_In, @Wafer_COST, @Qty_IN, @Die_Cost, @ProcessStep_ID, @Lot_Status, @P_Type, @P_Parent_Product_ID
		FETCH NEXT FROM @LOT_CURSOR INTO  @Wafer_Qty_In, @Qty_IN, @ProcessStep_ID, @Lot_Status, @P_Type, @P_Parent_Product_ID
	END
	CLOSE @LOT_CURSOR
	DEALLOCATE @LOT_CURSOR


	
	SET @START_DATE = NULL
	SET @END_DATE = NULL
	SET @START_DATE = @ASOFMonth + '/'+ '01/' + @ASOFYEAR
	SET @END_DATE = DATEADD( mi, -1, DATEADD( m, 1, @START_DATE ) )
	--SET @MONTH_START_DATE = DATEADD( m, 1, @START_DATE )
	--SET @MONTH_END_DATE = DATEADD( mi, -1, DATEADD( d, 1, @MONTH_START_DATE ) )
	/*PRINT '@START_DATE  ' + CONVERT( VARCHAR, @START_DATE )
	PRINT '@END_DATE  ' + CONVERT( VARCHAR, @END_DATE )*/
	PRINT '@MONTH_START_DATE @@@  ' + CONVERT( VARCHAR, @START_DATE )
	PRINT '@MONTH_END_DATE  @@@@@@@' + CONVERT( VARCHAR, @END_DATE )

	SET @Shipment_CURSOR =  CURSOR FOR SELECT SUM(CSLNT.CSLNT_Qty_Pull), P.ProcessStep_Id,CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID  FROM 
	Customer_Shipping_lot_number_Table CSLNT, Customer_Shipping_Line_item_table CSLIT, Lot_table LT, Product P 
	WHERE CSLNT.CSLIT_ID = CSLIT.CSLIT_ID
	AND CSLNT.Lot_Id = LT.Lot_ID 
	AND Lt.ProcessStep_Id = P.ProcessStep_ID
	AND CSLNT.CSLNT_Status = 'SHIPPED' AND CSLNT_Qty_Pull > 0 
	AND CSLIT_Shipped_Date BETWEEN @START_DATE AND @END_DATE 
	GROUP BY P.ProcessStep_Id, P.P_Parent_Product_ID, P.Product_ID

	OPEN @Shipment_CURSOR
	FETCH NEXT FROM @Shipment_CURSOR INTO @Qty_IN, @ProcessStep_ID, @P_Parent_Product_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--SELECT @Die_Cost = ISNULL(Q2_Standard_Cost, 0.0) FROM Update_View_Cost_Table WHERE ProcessStep_Id = @ProcessStep_ID
		--Update @RESULTS SET [Total Shipped Units] = ISNULL( [Total Shipped Units], 0) + @Qty_IN, [Total Shipment Cost] = ISNULL([Total Shipment Cost], 0.0) + ( @Qty_IN * @Die_Cost ) WHERE [Base Product ID] = @P_Parent_Product_ID
		Update @RESULTS SET [Total Shipped Units] = ISNULL( [Total Shipped Units], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		FETCH NEXT FROM @Shipment_CURSOR INTO @Qty_IN, @ProcessStep_ID, @P_Parent_Product_ID
	END
	CLOSE @Shipment_CURSOR
	DEALLOCATE @Shipment_CURSOR

	SET @FAB_Transit_CURSOR = CURSOR FOR SELECT SUM(Ship_Qty), P.ProcessStep_ID,CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID,
	ISNULL((SELECT ISNULL(Parameter_Value, 0) FROM Process_parameter WHERE ProcessStep_Id = P.ProcessStep_ID AND Parameter_Key = 'Gross Die/Wafer' ), 0)
	FROM Shipped_Lot SL, Lot_Table LT, Process_Step PS, Product P 
	WHERE SL.Lot_ID = Lt.Lot_ID AND LT.ProcessStep_ID = PS.ProcessStep_ID AND 
	P.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type_Name = 'Fab'
	AND Ship_Date BETWEEN @START_DATE AND @END_DATE 
	GROUP BY P.ProcessStep_ID, P.P_Parent_Product_ID, P.Product_ID
	
	OPEN @FAB_Transit_CURSOR
	FETCH NEXT FROM @FAB_Transit_CURSOR INTO @Qty_IN, @ProcessStep_ID, @P_Parent_Product_ID,@Gross_Die_Wafer
	WHILE @@FETCH_STATUS = 0
	BEGIN
		Update @RESULTS SET [Wafer Input] = ISNULL( [Wafer Input], 0) + @Qty_IN WHERE [Base Product ID] = @P_Parent_Product_ID
		FETCH NEXT FROM @FAB_Transit_CURSOR INTO @Qty_IN, @ProcessStep_ID, @P_Parent_Product_ID, @Gross_Die_Wafer
	END
	CLOSE @FAB_Transit_CURSOR
	DEALLOCATE @FAB_Transit_CURSOR
/*
	SET @FAB_Month_Start_CURSOR = CURSOR FOR SELECT SUM(Wafer_Qty_IN), P.ProcessStep_ID,CASE WHEN P.P_Parent_Product_ID > 1 THEN P.P_Parent_Product_ID ELSE P.Product_ID END AS Parent_Product_ID,
	ISNULL((SELECT ISNULL(Parameter_Value, 0) FROM Process_parameter WHERE ProcessStep_Id = P.ProcessStep_ID AND Parameter_Key = 'Gross Die/Wafer' ), 0)
	FROM Lot_Table_History LTH, Process_Step PS, Product P 
	WHERE LTH.ProcessStep_ID = PS.ProcessStep_ID AND 
	P.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type_Name = 'Fab'
	AND LTH.TIMESTAMP >= @MONTH_START_DATE AND LTH.TIMESTAMP <= @MONTH_END_DATE
	GROUP BY P.ProcessStep_ID, P.P_Parent_Product_ID, P.Product_ID
	
	OPEN @FAB_Month_Start_CURSOR
	FETCH NEXT FROM @FAB_Month_Start_CURSOR INTO @Qty_IN, @ProcessStep_ID, @P_Parent_Product_ID,@Gross_Die_Wafer
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT '@Month_Start_Wafer_Qty_In:'+ convert(varchar, ISNULL( @Qty_IN, 0 ))
		Update @RESULTS SET [Month Start Wafer Qty] = ISNULL( [Month Start Wafer Qty], 0) + @Qty_IN, [Month Start Effective Die] = ISNULL( [Month Start Effective Die], 0 ) + ISNULL( ( @Qty_IN * @Gross_Die_Wafer ), 0 ) WHERE [Base Product ID] = @P_Parent_Product_ID
		FETCH NEXT FROM @FAB_Month_Start_CURSOR INTO @Qty_IN, @ProcessStep_ID, @P_Parent_Product_ID, @Gross_Die_Wafer
	END
	CLOSE @FAB_Month_Start_CURSOR
	DEALLOCATE @FAB_Month_Start_CURSOR

	UPDATE @RESULTS SET [Wafer Input] = ISNULL( [Month Start Wafer Qty] , 0) + ISNULL( [Wafer Input], 0 ) - ISNULL( [Fab Wafer Qty], 0 ),
	[Effective Die] = ISNULL( [Month Start Effective Die], 0 ) + ISNULL( [Effective Die], 0 ) - ISNULL( [Fab Effective Die], 0 ) 
	WHERE [Month Start Wafer Qty] IS NOT NULL OR [Wafer Input] IS NOT NULL OR [Fab Wafer Qty] IS NOT NULL
	
	UPDATE @RESULTS SET [Wafer Input] = 0, [Effective Die] = 0 WHERE [Wafer Input] IS NOT NULL AND [Wafer Input] < 0

*/
	SELECT [BASE PART], [Wafer Input], [Effective Die], [Die Bank Start Qty], [Die Bank End Qty], (ISNULL([Die Bank End Qty], 0) - ISNULL([Die Bank Start Qty], 0) ) As [Die Bank Delta], [Assembly Die Start Qty], [Assembly Die End Qty], (ISNULL([Assembly Die End Qty], 0) - ISNULL([Assembly Die Start Qty], 0) ) As [Assembly Delta], [Test-FT Die Start Qty], [Test-FT Die End Qty], (ISNULL([Test-FT Die End Qty], 0) - ISNULL([Test-FT Die Start Qty], 0) ) As [Test-Ft Delta], [FINISHED GOODS Start Qty], [Finished Goods End Qty], (ISNULL([Finished Goods End Qty], 0) - ISNULL([FINISHED GOODS Start Qty], 0) ) As [FG Delta], [Total Shipped Units], [Total Backlog] FROM @RESULTS ORDER BY [BASE PART]
END